package org.huawei.dao;

import org.huawei.model.domain.Homework;
import org.huawei.model.domain.User;
import org.huawei.utils.DatabaseUtil;
import org.huawei.view.AlartView;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class HomeworkDao {

    public List<Homework> getAllHomeworks() throws SQLException {
        List<Homework> homeworks = new ArrayList<>();
        String query = "SELECT * FROM homework";
        try (Connection conn = DatabaseUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {
            while (rs.next()) {
                Homework homework = mapResultSetToHomework(rs);
                homeworks.add(homework);
            }
        }
        return homeworks;
    }

    public List<Homework> getHomeworksByUserId(User user) throws SQLException {
        List<Homework> homeworks = new ArrayList<>();
        String query = "SELECT * FROM homework WHERE CreateBy = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {
            stmt.setInt(1, user.getUserId());
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Homework homework = mapResultSetToHomework(rs);
                    homeworks.add(homework);
                }
            }
        }
        return homeworks;
    }


    private Homework mapResultSetToHomework(ResultSet rs) throws SQLException {
        Homework homework = new Homework();
        homework.setHomeworkId(rs.getInt("HomeworkId"));
        homework.setCreateBy(rs.getInt("CreateBy"));
        homework.setHomeworkName(rs.getString("HomeworkName"));
        homework.setHomeworkDescription(rs.getString("HomeworkDescription"));
        homework.setSubmissionTimes(rs.getInt("SubmissionTimes"));
        homework.setHomeworkGrade(rs.getDouble("HomeworkGrade"));
        // 将数据库中的字符串转换为枚举类型
        String homeworkStatusStr = rs.getString("HomeworkStatus");
        Homework.HomeworkStatus homeworkStatus = Homework.HomeworkStatus.valueOf(homeworkStatusStr.toUpperCase());
        homework.setHomeworkStatus(homeworkStatus);

        homework.setHomeworkCreationDate(rs.getDate("HomeworkCreationDate"));
        homework.setHomeworkUpdateDate(rs.getDate("HomeworkUpdateDate"));
        homework.setHomeworkEndDate(rs.getDate("HomeworkEndDate"));
        return homework;
    }

    public void addHomework(Homework homework) {
        String query = "INSERT INTO homework (CreateBy, HomeworkName, HomeworkDescription, SubmissionTimes, HomeworkGrade, HomeworkStatus, HomeworkCreationDate, HomeworkUpdateDate, HomeworkEndDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS)) {  // 添加 RETURN_GENERATED_KEYS

            // Set parameters for the PreparedStatement
            stmt.setInt(1, homework.getCreateBy());
            stmt.setString(2, homework.getHomeworkName());
            stmt.setString(3, homework.getHomeworkDescription());
            stmt.setInt(4, homework.getSubmissionTimes());
            stmt.setDouble(5, homework.getHomeworkGrade());
            stmt.setString(6, homework.getHomeworkStatus().name());
            stmt.setDate(7, new Date(System.currentTimeMillis()));
            stmt.setDate(8, new Date(System.currentTimeMillis()));
            java.util.Date utilDate = homework.getHomeworkEndDate();
            java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
            stmt.setDate(9, sqlDate);

            // 执行插入操作
            int affectedRows = stmt.executeUpdate();

            // 检查是否插入成功
            if (affectedRows == 0) {
                throw new SQLException("插入作业失败，未生成任何行。");
            }

            // 获取生成的 HomeworkId
            try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    homework.setHomeworkId(generatedKeys.getInt(1));
                } else {
                    throw new SQLException("插入作业失败，未能获取生成的 HomeworkId。");
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    public void updateHomework(Homework homework) throws SQLException {
        String query = "UPDATE homework SET HomeworkName = ?, HomeworkDescription = ?, HomeworkStatus = ?, HomeworkUpdateDate = ?, HomeworkEndDate = ? WHERE HomeworkId = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {

            stmt.setString(1, homework.getHomeworkName());
            stmt.setString(2, homework.getHomeworkDescription());
            stmt.setString(3, homework.getHomeworkStatus().name());

            // 将 java.util.Date 转换为 java.sql.Date
            java.util.Date utilDate1 = homework.getHomeworkUpdateDate();
            java.sql.Date sqlDate1 = new java.sql.Date(utilDate1.getTime());
            stmt.setDate(4, sqlDate1);

            java.util.Date utilDate2 = homework.getHomeworkEndDate();
            java.sql.Date sqlDate2 = new java.sql.Date(utilDate2.getTime());
            stmt.setDate(5, sqlDate2);

            // 设置第6个参数 HomeworkId
            stmt.setInt(6, homework.getHomeworkId());

            stmt.executeUpdate();
        } catch (SQLException e) {
            AlartView.alart(null,"更新失败");
            e.printStackTrace();
        }
    }

    public void updateAverageGrade(int homeworkId) throws SQLException {
        String query = "UPDATE Homework SET HomeworkGrade = (SELECT AVG(SubmissionGrade) FROM Submission WHERE HomeworkId = ?) WHERE HomeworkId = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {

            stmt.setInt(1, homeworkId);
            stmt.setInt(2, homeworkId);

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException("Error updating average grade for HomeworkId: " + homeworkId, e);
        }
    }


    public void updateSubmissionTimes(int homeworkId) throws SQLException {
        String query = "UPDATE Homework SET SubmissionTimes = (SELECT COUNT(*) FROM Submission WHERE HomeworkId = ?) WHERE HomeworkId = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {

            stmt.setInt(1, homeworkId);
            stmt.setInt(2, homeworkId);

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException("Error updating submission times for HomeworkId: " + homeworkId, e);
        }
    }

    public void updateHomeworkStatus(int homeworkId) throws SQLException {
        String query = "UPDATE Homework SET HomeworkStatus = CASE " +
                "WHEN HomeworkStatus = 'Assigned' AND HomeworkEndDate < CURDATE() THEN 'Overdue' " +
                "ELSE HomeworkStatus END " +
                "WHERE HomeworkId = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {

            stmt.setInt(1, homeworkId);

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException("Error updating homework status for HomeworkId: " + homeworkId, e);
        }
    }


    public List<Integer> getAllHomeworkIds() throws SQLException {
        List<Integer> homeworkIds = new ArrayList<>();
        String query = "SELECT HomeworkId FROM Homework";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                homeworkIds.add(rs.getInt("HomeworkId"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException("Error fetching all Homework IDs.", e);
        }
        return homeworkIds;
    }

    public List<Homework> fetchHomeworksForStudent(int studentId) {
        List<Homework> homeworkList = new ArrayList<>();
        String query = "SELECT h.HomeworkName, h.HomeworkDescription, h.HomeworkEndDate, h.HomeworkStatus, h.HomeworkId " +
                "FROM homework h " +
                "JOIN homework_student hs ON h.HomeworkId = hs.HomeworkId " +
                "WHERE hs.StudentId = ?";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {
            stmt.setInt(1, studentId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Homework homework = new Homework();
                homework.setHomeworkName(rs.getString("HomeworkName"));
                homework.setHomeworkDescription(rs.getString("HomeworkDescription"));
                homework.setHomeworkEndDate(rs.getDate("HomeworkEndDate"));
                homework.setHomeworkStatus(Homework.HomeworkStatus.valueOf(rs.getString("homeworkStatus").toUpperCase()));
                homework.setHomeworkId(rs.getInt("HomeworkId"));
                homeworkList.add(homework);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return homeworkList;
    }

}
